const { pool } = require('../config/db');
const { success, error } = require('../utils/response');
const moment = require('moment');

// 创建预约
exports.createReservation = async (req, res, next) => {
  try {
    const { bookId } = req.body;
    const userId = req.userId;

    // 检查图书是否存在
    const [books] = await pool.query(
      'SELECT id, name, stock FROM books WHERE id = ?',
      [bookId]
    );

    if (books.length === 0) {
      return error(res, '图书不存在', 400);
    }

    const book = books[0];

    // 检查库存
    if (book.stock > 0) {
      return error(res, '图书有库存，请直接借阅', 400);
    }

    // 检查是否已经预约过此书
    const [existingReservations] = await pool.query(
      'SELECT id FROM reservations WHERE user_id = ? AND book_id = ? AND status IN ("waiting", "available")',
      [userId, bookId]
    );

    if (existingReservations.length > 0) {
      return error(res, '您已经预约过此书', 400);
    }

    // 检查预约数量是否超过限制（每个用户最多预约5本）
    const [userReservations] = await pool.query(
      'SELECT COUNT(*) as count FROM reservations WHERE user_id = ? AND status IN ("waiting", "available")',
      [userId]
    );

    if (userReservations[0].count >= 5) {
      return error(res, '最多只能预约5本书', 400);
    }

    // 计算过期时间
    const expiryDays = parseInt(process.env.RESERVATION_EXPIRY_DAYS) || 7;
    const expiryDate = moment().add(expiryDays, 'days').format('YYYY-MM-DD HH:mm:ss');

    // 创建预约记录
    const [result] = await pool.query(
      'INSERT INTO reservations (user_id, book_id, expiry_date) VALUES (?, ?, ?)',
      [userId, bookId, expiryDate]
    );

    success(res, {
      reservationId: result.insertId,
      bookName: book.name,
      expiryDate: expiryDate,
      expiryDays: expiryDays
    }, '预约成功');

  } catch (err) {
    next(err);
  }
};

// 获取用户的预约记录
exports.getReservations = async (req, res, next) => {
  try {
    const userId = req.userId;
    const { status } = req.query; // waiting, available, cancelled, expired

    let whereClause = 'WHERE r.user_id = ?';
    let params = [userId];

    if (status) {
      whereClause += ' AND r.status = ?';
      params.push(status);
    }

    const [reservations] = await pool.query(`
      SELECT
        r.id,
        r.reservation_date,
        r.expiry_date,
        r.status,
        r.notify_sent,
        b.id as book_id,
        b.name as book_name,
        b.author,
        b.cover
      FROM reservations r
      LEFT JOIN books b ON r.book_id = b.id
      ${whereClause}
      ORDER BY r.reservation_date DESC
    `, params);

    success(res, reservations);
  } catch (err) {
    next(err);
  }
};

// 取消预约
exports.cancelReservation = async (req, res, next) => {
  try {
    const { reservationId } = req.body;
    const userId = req.userId;

    // 获取预约记录
    const [reservations] = await pool.query(
      'SELECT * FROM reservations WHERE id = ? AND user_id = ? AND status IN ("waiting", "available")',
      [reservationId, userId]
    );

    if (reservations.length === 0) {
      return error(res, '预约记录不存在或无法取消', 404);
    }

    // 更新预约状态
    await pool.query(
      'UPDATE reservations SET status = "cancelled" WHERE id = ?',
      [reservationId]
    );

    success(res, {
      reservationId: reservationId,
      cancelTime: moment().format('YYYY-MM-DD HH:mm:ss')
    }, '预约取消成功');

  } catch (err) {
    next(err);
  }
};

// 获取可预约的图书（管理员接口）
exports.getAvailableBooks = async (req, res, next) => {
  try {
    const { page = 1, limit = 10, category, keyword } = req.query;

    const offset = (page - 1) * limit;
    let whereClause = 'WHERE b.stock = 0';
    let params = [];

    if (category) {
      whereClause += ' AND b.category_id = ?';
      params.push(category);
    }

    if (keyword) {
      whereClause += ' AND (b.name LIKE ? OR b.author LIKE ?)';
      params.push(`%${keyword}%`, `%${keyword}%`);
    }

    const [books] = await pool.query(`
      SELECT
        b.id,
        b.name,
        b.author,
        b.category,
        b.cover,
        b.price,
        c.name as category_name,
        COUNT(r.id) as reservation_count
      FROM books b
      LEFT JOIN categories c ON b.category_id = c.id
      LEFT JOIN reservations r ON b.id = r.book_id AND r.status IN ('waiting', 'available')
      ${whereClause}
      GROUP BY b.id
      ORDER BY reservation_count ASC, b.name
      LIMIT ? OFFSET ?
    `, [...params, parseInt(limit), offset]);

    // 获取总数
    const [total] = await pool.query(`
      SELECT COUNT(*) as count FROM books b ${whereClause}
    `, params);

    success(res, {
      books,
      pagination: {
        current: parseInt(page),
        limit: parseInt(limit),
        total: total[0].count,
        pages: Math.ceil(total[0].count / limit)
      }
    });
  } catch (err) {
    next(err);
  }
};

// 检查预约到期
exports.checkExpiredReservations = async () => {
  try {
    const now = moment().format('YYYY-MM-DD HH:mm:ss');

    // 获取过期预约
    const [expiredReservations] = await pool.query(
      'SELECT * FROM reservations WHERE expiry_date < ? AND status = "waiting"',
      [now]
    );

    if (expiredReservations.length > 0) {
      // 更新过期预约状态
      await pool.query(
        'UPDATE reservations SET status = "expired" WHERE expiry_date < ? AND status = "waiting"',
        [now]
      );

      console.log(`清理了 ${expiredReservations.length} 个过期预约`);
    }

    return expiredReservations.length;
  } catch (err) {
    console.error('检查过期预约失败:', err);
    return 0;
  }
};

// ===== 管理员端接口 =====

// 获取所有预约（管理员）
exports.getAllReservations = async (req, res, next) => {
  try {
    const { page = 1, limit = 10, status, keyword } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE 1=1';
    let params = [];

    if (status) {
      whereClause += ' AND r.status = ?';
      params.push(status);
    }

    if (keyword) {
      whereClause += ' AND (b.name LIKE ? OR u.username LIKE ? OR u.phone LIKE ?)';
      params.push(`%${keyword}%`, `%${keyword}%`, `%${keyword}%`);
    }

    const [reservations] = await pool.query(`
      SELECT
        r.id,
        r.reservation_date,
        r.expiry_date,
        r.status,
        r.notify_sent,
        u.id as user_id,
        u.username,
        u.phone,
        u.user_type,
        b.id as book_id,
        b.name as book_name,
        b.author,
        b.cover,
        b.stock
      FROM reservations r
      LEFT JOIN users u ON r.user_id = u.id
      LEFT JOIN books b ON r.book_id = b.id
      ${whereClause}
      ORDER BY r.reservation_date ASC
      LIMIT ? OFFSET ?
    `, [...params, parseInt(limit), offset]);

    const [total] = await pool.query(`
      SELECT COUNT(*) as count 
      FROM reservations r
      LEFT JOIN books b ON r.book_id = b.id
      LEFT JOIN users u ON r.user_id = u.id
      ${whereClause}
    `, params);

    success(res, {
      reservations,
      pagination: {
        current: parseInt(page),
        limit: parseInt(limit),
        total: total[0].count,
        pages: Math.ceil(total[0].count / limit)
      }
    });
  } catch (err) {
    next(err);
  }
};

// 通知预约用户（管理员）
exports.notifyReservation = async (req, res, next) => {
  try {
    const { reservationId } = req.params;
    const { sendNotificationController } = require('./notificationController');

    // 获取预约信息
    const [reservations] = await pool.query(`
      SELECT
        r.*,
        u.username,
        b.name as book_name
      FROM reservations r
      LEFT JOIN users u ON r.user_id = u.id
      LEFT JOIN books b ON r.book_id = b.id
      WHERE r.id = ? AND r.status = 'waiting'
    `, [reservationId]);

    if (reservations.length === 0) {
      return error(res, '预约记录不存在或状态不正确', 404);
    }

    const reservation = reservations[0];

    // 更新预约状态为可借
    await pool.query(
      'UPDATE reservations SET status = "available", notify_sent = 1 WHERE id = ?',
      [reservationId]
    );

    // 发送通知
    const { sendReservationAvailableNotice } = require('./notificationController');
    await sendReservationAvailableNotice(
      reservationId,
      reservation.user_id,
      reservation.book_name
    );

    success(res, {
      reservationId,
      userName: reservation.username,
      bookName: reservation.book_name
    }, '通知发送成功');

  } catch (err) {
    next(err);
  }
};

// 获取预约统计（管理员）
exports.getReservationStats = async (req, res, next) => {
  try {
    const [stats] = await pool.query(`
      SELECT
        COUNT(*) as total_count,
        SUM(CASE WHEN status = 'waiting' THEN 1 ELSE 0 END) as waiting_count,
        SUM(CASE WHEN status = 'available' THEN 1 ELSE 0 END) as available_count,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count,
        SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) as expired_count
      FROM reservations
    `);

    success(res, stats[0]);
  } catch (err) {
    next(err);
  }
};

// 图书归还时自动通知预约用户
exports.notifyNextReservation = async (bookId) => {
  try {
    // 获取该图书的第一个等待中的预约
    const [reservations] = await pool.query(`
      SELECT
        r.*,
        b.name as book_name
      FROM reservations r
      LEFT JOIN books b ON r.book_id = b.id
      WHERE r.book_id = ? AND r.status = 'waiting'
      ORDER BY r.reservation_date ASC
      LIMIT 1
    `, [bookId]);

    if (reservations.length > 0) {
      const reservation = reservations[0];

      // 更新预约状态为可借
      await pool.query(
        'UPDATE reservations SET status = "available", notify_sent = 1 WHERE id = ?',
        [reservation.id]
      );

      // 发送通知
      const { sendReservationAvailableNotice } = require('./notificationController');
      await sendReservationAvailableNotice(
        reservation.id,
        reservation.user_id,
        reservation.book_name
      );

      console.log(`已通知预约用户 ${reservation.user_id}，图书《${reservation.book_name}》可借`);
      return true;
    }

    return false;
  } catch (err) {
    console.error('通知预约用户失败:', err);
    return false;
  }
};

// 导出供其他模块使用
module.exports.notifyNextReservation = exports.notifyNextReservation;